postgresql的一些固定用法

工作中用pg,有些语法和其他的并不一样，记录下。

• 查看数据库中各个表（索引）大小

select
    (n.nspname::text),
    c.relname::text as relation,
    pg_size_pretty(pg_total_relation_size(c.oid::regclass)) as total_table_size,
    pg_size_pretty(pg_indexes_size(c.oid::regclass)) as total_index_size
from
    pg_class c
    left join pg_tablespace t on c.reltablespace = t.oid
    left join pg_namespace n on n.oid = c.relnamespace
where (n.nspname <> all (array['pg_catalog'::name, 'information_schema'::name, 'pg_toast'::name]))
    and c.relkind = 'r'::"char"
order by
    (pg_total_relation_size(c.oid::regclass))
    desc;

• 查看各个数据库的大小

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;

• 查看表的各个字段

   select * from information_schema.columns where table_schema='test_schema' and table_name='test_table';

• 关联更新

update
    tmp.order_table
set
    create_time = tmp.test_order.create_time
from
    tmp.test_order
where
    tmp.test_order.id = tmp.order_table.id;

• 去掉字段的非空限制

alter table test.table_name alter column column_1 drop not null;

repeat&regexp_split_to_table方法
在某OTA业务中某规则需按照实际的份数和支付时间来计算黄牛和刷单，但是订单表是按照订单来记录的，例如订单A这个订单包含了2份产品，这五份对用的支付时间都是一样的，pg中可以将这个订单用repect方法拆分：

select order_id,substring(repeat(',' || a.pay_time, b.quantity::int), 2) pay_time from order_table a left join  order_table b on a.order_id = b.order_id

得到的结果如下：
order_id pay_time
A 2014-01-10 13:44:44.927, 2014-01-10 13:44:44.927
在用regexp_split_to_table方法将一行数据拆分到多行：
select order_id,regexp_split_to_table(pay_time) from before_table
得到按照份数粒度的结果表如下：
order_id pay_time
A 2014-01-10 13:44:44.927
A 2014-01-10 13:44:44.927